using System;
using System.Data;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using WorldGoldenPrice;
using SJCGoldenPrice;
using WorldIndexInfo;
using System.Configuration;
using System.Xml;
namespace DatabaseService
{
    public class AccessDB
    {

        private static SqlConnection getConnect()
        {
            return new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"].Trim());
        }



        public static bool InsertGoldenPriceDB(GoldenPrice_Factory obj, string Provider)
        {
            bool bResult = false;
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();

                SqlCommand cmd = new SqlCommand("[market].[usp_ActionProduct]", l_SQLConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                XmlDocument doc = new XmlDocument();
                XmlElement InputValue = doc.CreateElement("InputValue");
                InputValue.SetAttribute("WorldBuy", obj.World_Buy);
                InputValue.SetAttribute("WorldSell", obj.World_Sell);
                InputValue.SetAttribute("VnBuy", obj.Vn_Buy);
                InputValue.SetAttribute("VnSell", obj.Vn_Sell);
                InputValue.SetAttribute("OilBuy", obj.OilBuy);
                InputValue.SetAttribute("OilSell", obj.OilSell);
                InputValue.SetAttribute("Provider", Provider);
                InputValue.SetAttribute("EURUSD", obj.EURUSD);
                InputValue.SetAttribute("GBPUSD", obj.GBPUSD);
                InputValue.SetAttribute("USDJPY", obj.USDJPY);
                InputValue.SetAttribute("USDCAD", obj.USDCAD);
                InputValue.SetAttribute("AUDUSD", obj.AUDUSD);
                InputValue.SetAttribute("NZDUSD", " ");
                InputValue.SetAttribute("USDNOK", "");
                InputValue.SetAttribute("Provider", Provider);
                cmd.Parameters.AddWithValue("@InputValue", InputValue.OuterXml);
                if (cmd.ExecuteNonQuery() > 0)
                    bResult = false;

            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : InsertGoldenPriceDB", ex.ToString());
            }
            finally
            {

                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }

        public static bool InsertSJCPriceDB(SJC_Factory obj, string Provider)
        {
            bool bResult = false;
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();
                string strSQL = "insert into [MarketInfo.SJC](HaNoiBuy,HaNoiSell,HcmBuy,HcmSell,DaNangBuy,DaNangSell,NhaTrangBuy,NhaTrangSell,CanThoBuy,CanThoSell, Provider, DateModified) values(@HaNoiBuy,@HaNoiSell,@HcmBuy,@HcmSell,@DaNangBuy,@DaNangSell,@NhaTrangBuy,@NhaTrangSell,@CanThoBuy,@CanThoSell, @Provider, @DateModified) ";

                SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@HaNoiBuy", obj.Ha_noi.Mua);
                cmd.Parameters.AddWithValue("@HaNoiSell", obj.Ha_noi.Ban);
                cmd.Parameters.AddWithValue("@HcmBuy", obj.Hcm.Mua);
                cmd.Parameters.AddWithValue("@HcmSell", obj.Hcm.Ban);
                cmd.Parameters.AddWithValue("@DaNangBuy", obj.Da_nang.Mua);
                cmd.Parameters.AddWithValue("@DaNangSell", obj.Da_nang.Ban);
                cmd.Parameters.AddWithValue("@CanThoBuy", obj.Can_tho.Mua);
                cmd.Parameters.AddWithValue("@CanThoSell", obj.Can_tho.Ban);
                cmd.Parameters.AddWithValue("@NhaTrangBuy", obj.Nha_trang.Mua);
                cmd.Parameters.AddWithValue("@NhaTrangSell", obj.Nha_trang.Ban);
                cmd.Parameters.AddWithValue("@Provider", Provider);
                cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);


                if (cmd.ExecuteNonQuery() > 0)
                    bResult = false;

            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : InsertSJCPriceDB", ex.ToString());
            }
            finally
            {

                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }
        public static bool InsertExchangeDB(WorldExchange.Exchange_Factory obj, string Provider)
        {
            bool bResult = false;
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();


                SqlCommand cmd = new SqlCommand("[market].[usp_ActionExchange]", l_SQLConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                XmlDocument doc = new XmlDocument();
                XmlElement InputValue = doc.CreateElement("InputValue");
                InputValue.SetAttribute("USDSell", obj.USD.Ban);
                InputValue.SetAttribute("USDBuy", obj.USD.Mua_TM);
                InputValue.SetAttribute("USDBuyTransfer", obj.USD.Mua_CK);

                InputValue.SetAttribute("EURSell", obj.EUR.Ban);
                InputValue.SetAttribute("EURBuy", obj.EUR.Mua_TM);
                InputValue.SetAttribute("EURBuyTransfer", obj.EUR.Mua_CK);

                InputValue.SetAttribute("GBPSell", obj.GBP.Ban);
                InputValue.SetAttribute("GBPBuy", obj.GBP.Mua_TM);
                InputValue.SetAttribute("GBPBuyTransfer", obj.GBP.Mua_CK);

                InputValue.SetAttribute("AUDSell", obj.AUD.Ban);
                InputValue.SetAttribute("AUDBuy", obj.AUD.Mua_TM);
                InputValue.SetAttribute("AUDBuyTransfer", obj.AUD.Mua_CK);

                InputValue.SetAttribute("CHFSell", obj.CHF.Ban);
                InputValue.SetAttribute("CHFBuy", obj.CHF.Mua_TM);
                InputValue.SetAttribute("CHFBuyTransfer", obj.CHF.Mua_CK);

                InputValue.SetAttribute("JPYSell", obj.JPY.Ban);
                InputValue.SetAttribute("JPYBuy", obj.JPY.Mua_TM);
                InputValue.SetAttribute("JPYBuyTransfer", obj.JPY.Mua_CK);

                InputValue.SetAttribute("SGDSell", obj.SGD.Ban);
                InputValue.SetAttribute("SGDBuy", obj.SGD.Mua_TM);
                InputValue.SetAttribute("SGDBuyTransfer", obj.SGD.Mua_CK);
                InputValue.SetAttribute("Provider", Provider);


                cmd.Parameters.AddWithValue("@InputValue", InputValue.OuterXml);
                if (cmd.ExecuteNonQuery() > 0)
                    bResult = false;


            }
            catch (Exception ex)
            {
                //throw (ex);
                TLog.WriteErr("DataAccess : InsertExchangeDB", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }
        public static WorldExchange.Exchange_Factory getExchangeInfo()
        {
            DataTable dt = new DataTable(); ;
            WorldExchange.Exchange_Factory objResult = new WorldExchange.Exchange_Factory();
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();
                string strSQL = "select top 1 * from [MarketInfo.Exchange] order  by ExchangeID DESC  ";
                SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
                adap.Fill(dt);

            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : getExchangeInfo", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            if (dt.Rows.Count > 0)
            {
                objResult.USD = SetValue(dt.Rows[0]["USDSell"], dt.Rows[0]["USDBuy"], dt.Rows[0]["USDBuyTransfer"]);
                objResult.EUR = SetValue(dt.Rows[0]["EURSell"], dt.Rows[0]["EURBuy"], dt.Rows[0]["EURBuyTransfer"]);
                objResult.GBP = SetValue(dt.Rows[0]["GBPSell"], dt.Rows[0]["GBPBuy"], dt.Rows[0]["GBPBuyTransfer"]);
                objResult.AUD = SetValue(dt.Rows[0]["AUDSell"], dt.Rows[0]["AUDBuy"], dt.Rows[0]["AUDBuyTransfer"]);
                objResult.CHF = SetValue(dt.Rows[0]["CHFSell"], dt.Rows[0]["CHFBuy"], dt.Rows[0]["CHFBuyTransfer"]);
                objResult.JPY = SetValue(dt.Rows[0]["JPYSell"], dt.Rows[0]["JPYBuy"], dt.Rows[0]["JPYBuyTransfer"]);
                objResult.SGD = SetValue(dt.Rows[0]["SGDSell"], dt.Rows[0]["SGDBuy"], dt.Rows[0]["SGDBuyTransfer"]);
                //objResult.USD = SetValue(dt.Rows[0]["USD_sell"], dt.Rows[0]["USD_buy"], dt.Rows[0]["USD_buytransfer"]);
            }

            return objResult;

        }

        public static GoldenPrice_Factory getProductInfo()
        {
            DataTable dt = new DataTable(); ;
            GoldenPrice_Factory objResult = new GoldenPrice_Factory();
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();
                string strSQL = "select top 1 * from [MarketInfo.Product] order  by ProductID DESC  ";
                SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
                adap.Fill(dt);

            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : getProductInfo", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["OilBuy"] != DBNull.Value)
                    objResult.OilBuy = dt.Rows[0]["OilBuy"].ToString().Trim();
                if (dt.Rows[0]["OilSell"] != DBNull.Value)
                    objResult.OilSell = dt.Rows[0]["OilSell"].ToString().Trim();
                if (dt.Rows[0]["EURUSD"] != DBNull.Value)
                    objResult.EURUSD = dt.Rows[0]["EURUSD"].ToString().Trim();
                if (dt.Rows[0]["GBPUSD"] != DBNull.Value)
                    objResult.GBPUSD = dt.Rows[0]["GBPUSD"].ToString().Trim();
                if (dt.Rows[0]["USDJPY"] != DBNull.Value)
                    objResult.USDJPY = dt.Rows[0]["USDJPY"].ToString().Trim();
                if (dt.Rows[0]["USDCAD"] != DBNull.Value)
                    objResult.USDCAD = dt.Rows[0]["USDCAD"].ToString().Trim();
                if (dt.Rows[0]["AUDUSD"] != DBNull.Value)
                    objResult.AUDUSD = dt.Rows[0]["AUDUSD"].ToString().Trim();
                if (dt.Rows[0]["NZDUSD"] != DBNull.Value)
                    objResult.NZDUSD = dt.Rows[0]["NZDUSD"].ToString().Trim();
                if (dt.Rows[0]["USDNOK"] != DBNull.Value)
                    objResult.USDNOK = dt.Rows[0]["USDNOK"].ToString().Trim();


            }

            return objResult;

        }

        public static WorldExchange.Price SetValue(object Sell, object Buy, object Tran)
        {
            WorldExchange.Price objResult = new WorldExchange.Price();
            if (Sell != DBNull.Value)
                objResult.Ban = Sell.ToString();
            if (Buy != DBNull.Value)
                objResult.Mua_TM = Buy.ToString();
            if (Tran != DBNull.Value)
                objResult.Mua_CK = Tran.ToString();
            return objResult;

        }

        public static bool InsertWorldIndexDB(WorldIndexInfo.WorldIndex_Factory obj, string Provider)
        {
            bool bResult = false;
            SqlConnection l_SQLConnection = getConnect();
            try
            {
                l_SQLConnection.Open();

                //// string strSQL = "insert into tbl_Exchange(USD_buy,USD_buytransfer,USD_sell,EUR_buy,EUR_buytransfer,EUR_sell,GBP_buy,GBP_buytransfer,GBP_sell,AUD_buy,AUD_buytransfer,AUD_sell,CHF_buy,CHF_buytransfer,CHF_sell,JPY_buy,JPY_buytransfer,JPY_sell,SGD_buy,SGD_buytransfer,SGD_sell,Date_modified,Provider) ";
                //string strSQL = "insert into [MarketInfo.WorldIndex](DJIAPrice,DJIAChange,NasdaqPrice,NasdaqChange,FTSE100Price,FTSE100Change,NikkeiPrice,NikkeiChange,HSengPrice,HSengChange,DateModified,Provider) ";
                //strSQL += " values(@DJIA_Price,@DJIA_Change,@Nasdaq_Price,@Nasdaq_Change,@FTSE100_Price,@FTSE100_Change,@Nikkei_Price,@Nikkei_Change,@HSeng_Price,@HSeng_Change,@Date_modified,@Provider)";

                //SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
                //cmd.CommandType = CommandType.Text;

                //cmd.Parameters.AddWithValue("@DJIA_Price", obj.DJIAinfo.Value);
                //cmd.Parameters.AddWithValue("@DJIA_Change", obj.DJIAinfo.ChangeValue);


                //cmd.Parameters.AddWithValue("@Nasdaq_Price", obj.Nadaqinfo.Value);
                //cmd.Parameters.AddWithValue("@Nasdaq_Change", obj.Nadaqinfo.ChangeValue);


                //cmd.Parameters.AddWithValue("@FTSE100_Price", obj.Londoninfo.Value);
                //cmd.Parameters.AddWithValue("@FTSE100_Change", obj.Londoninfo.ChangeValue);


                //cmd.Parameters.AddWithValue("@Nikkei_Price", obj.Nikkei225info.Value);
                //cmd.Parameters.AddWithValue("@Nikkei_Change", obj.Nikkei225info.ChangeValue);


                //cmd.Parameters.AddWithValue("@HSeng_Price", obj.Hongkonginfo.Value);
                //cmd.Parameters.AddWithValue("@HSeng_Change", obj.Hongkonginfo.ChangeValue);

                //cmd.Parameters.AddWithValue("@Provider", Provider);
                //cmd.Parameters.AddWithValue("@Date_modified", DateTime.Now);






                SqlCommand cmd = new SqlCommand("[market].[usp_ActionWorldIndex]", l_SQLConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                XmlDocument doc = new XmlDocument();
                XmlElement InputValue = doc.CreateElement("InputValue");
                InputValue.SetAttribute("NadaqChangeValue", obj.Nadaqinfo.ChangeValue);
                InputValue.SetAttribute("NadaqChangePercent", obj.Nadaqinfo.ChangePercent);
                InputValue.SetAttribute("NadaqValue", obj.Nadaqinfo.Value);
                InputValue.SetAttribute("NadaqLocal", obj.Nadaqinfo.Local);

                InputValue.SetAttribute("LondonChangeValue", obj.Londoninfo.ChangeValue);
                InputValue.SetAttribute("LondonChangePercent", obj.Londoninfo.ChangePercent);
                InputValue.SetAttribute("LondonValue", obj.Londoninfo.Value);
                InputValue.SetAttribute("LondonLocal", obj.Londoninfo.Local);

                InputValue.SetAttribute("JASDAQChangeValue", obj.JASDAQinfo.ChangeValue);
                InputValue.SetAttribute("JASDAQChangePercent", obj.JASDAQinfo.ChangePercent);
                InputValue.SetAttribute("JASDAQValue", obj.JASDAQinfo.Value);
                InputValue.SetAttribute("JASDAQLocal", obj.JASDAQinfo.Local);

                InputValue.SetAttribute("RussiaChangeValue", obj.Russiainfo.ChangeValue);
                InputValue.SetAttribute("RussiaChangePercent", obj.Russiainfo.ChangePercent);
                InputValue.SetAttribute("RussiaValue", obj.Russiainfo.Value);
                InputValue.SetAttribute("RussiaLocal", obj.Russiainfo.Local);

                InputValue.SetAttribute("SP500ChangeValue", obj.SP500info.ChangeValue);
                InputValue.SetAttribute("SP500ChangePercent", obj.SP500info.ChangePercent);
                InputValue.SetAttribute("SP500Value", obj.SP500info.Value);
                InputValue.SetAttribute("SP500Local", obj.SP500info.Local);

                InputValue.SetAttribute("HongkongChangeValue", obj.Hongkonginfo.ChangeValue);
                InputValue.SetAttribute("HongkongChangePercent", obj.Hongkonginfo.ChangePercent);
                InputValue.SetAttribute("HongkongValue", obj.Hongkonginfo.Value);
                InputValue.SetAttribute("HongkongLocal", obj.Hongkonginfo.Local);

                InputValue.SetAttribute("Nikkei225ChangeValue", obj.Nikkei225info.ChangeValue);
                InputValue.SetAttribute("LNikkei225ChangePercent", obj.Nikkei225info.ChangePercent);
                InputValue.SetAttribute("Nikkei225Value", obj.Nikkei225info.Value);
                InputValue.SetAttribute("Nikkei225Local", obj.Nikkei225info.Local);

                InputValue.SetAttribute("Provider", Provider);
                cmd.Parameters.AddWithValue("@InputValue", InputValue.OuterXml);
                if (cmd.ExecuteNonQuery() > 0)
                    bResult = false;

            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : InsertWorldIndexDB", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }

        public static void UpdateVGBProfile()
        {

            SqlConnection l_SQLConnection = null;// getConnect();
            try
            {
                l_SQLConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectVGBProfile"].Trim());
                l_SQLConnection.Open();
                string strSQL = "Update ExchangeProcessStatus Set LastTimeProcess = GetDate(), LastServerProcess = @ServerName Where Service = @Service";
                SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Service", "MarketInfo");
                cmd.Parameters.AddWithValue("@ServerName", Environment.MachineName.Trim());
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : UpdateVGBProfile", ex.ToString());
            }

            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
        }
        /// <summary>
        /// 1 la duoc phep ghi,coi nhu cung server va  Now-lastupdate< ProductTimer
        /// 2 la duoc phep ghi khi khac server  va Now-lastupdate>ProductTimer
        /// 0 la ko duoc phep
        /// </summary>
        /// <returns></returns>
        public static int CheckRoleWrite()
        {
            string LastServerProcess = null;
            //string CurrentServerProcess = Environment.MachineName;
            DateTime LastTimeProcess;
            //DateTime CurrentTimeProcess = DateTime.Now;
            int ProductTimer = int.Parse(ConfigurationSettings.AppSettings["ProductTimer"].Trim());
            GetExchangeProcessStatus(out LastServerProcess, out LastTimeProcess);
            if (LastServerProcess == null || LastTimeProcess == null)
                return 1;
            if (LastTimeProcess.Add(new TimeSpan(0, 0, (ProductTimer + 1))) < DateTime.Now)
                return 2;

            if ((LastTimeProcess.Add(new TimeSpan(0, 0, (ProductTimer + 1))) >= DateTime.Now) && (LastServerProcess.Trim() == Environment.MachineName.Trim()))
                return 1;

            return 0;

        }

        public static void GetExchangeProcessStatus(out string LastServerProcess, out DateTime LastTimeProcess)
        {

            LastServerProcess = "";
            LastTimeProcess = DateTime.Now.AddDays(-1);
            SqlConnection l_SQLConnection = null;
            try
            {
                l_SQLConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectVGBProfile"].Trim());
                DataTable dt = new DataTable();
                l_SQLConnection.Open();
                string strSQL = "select top 1 * from [ExchangeProcessStatus] where Service='{0}'";
                strSQL = string.Format(strSQL, "MarketInfo");
                SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
                adap.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["LastTimeProcess"] != DBNull.Value)
                        LastTimeProcess = DateTime.Parse(dt.Rows[0]["LastTimeProcess"].ToString().Trim());
                    if (dt.Rows[0]["LastServerProcess"] != DBNull.Value)
                        LastServerProcess = dt.Rows[0]["LastServerProcess"].ToString().Trim();
                }



            }
            catch (Exception ex)
            {
                TLog.WriteErr("GetExchangeProcessStatus:", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }

            // return bResult;

        }

    }
}